﻿using System;
using System.Data;
using System.Text;
using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
using Model;

namespace DAL
{
    /// <summary>
    /// 数据访问类:PMS_Project
    /// </summary>
    public class PMS_ProjectDAL
    {
        public PMS_ProjectDAL()
        { }

        /// <summary>
        /// 根据项目编号查询数据
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public PMS_Project GetModel(int id)
        {
            string sql = "select * from dbo.PMS_Project where isDelete='0' and id=@id";
            SqlParameter[] values = { new SqlParameter("@id", id) };
            return SqlHelper.GetDataByColumns<PMS_Project>(Comm.Config.PMS, sql, values);
        }

        /// <summary>
        /// 根据项目编号查询数据
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public PMS_Project GetModel(string projectID, string projectType)
        {
            string sql = "select * from dbo.PMS_Project where isDelete='0' and projectID=@projectID and projectType=@projectType";
            SqlParameter[] values = { new SqlParameter("@projectID", projectID), new SqlParameter("@projectType", projectType) };
            return SqlHelper.GetDataByColumns<PMS_Project>(Comm.Config.PMS, sql, values);
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public List<PMS_Project> GetList(string whereStr)
        {
            string sql = "select * from dbo.PMS_Project where isDelete='0'";
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += " and " + whereStr;
            }
            sql += " order by createDate desc";
            return SqlHelper.GetDataByColumnsLst<PMS_Project>(Comm.Config.PMS, sql);
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public List<PMS_Project> GetList(PMS_ProjectQuery query, string roles)
        {
            string sql = "select * from dbo.PMS_Project where isDelete='0'";
            if (query.userID.HasValue)
            {
                sql += " and createName='" + query.userID + "' ";
            }
            if (!string.IsNullOrEmpty(query.projectType))
            {
                sql += " and projectType=N'" + query.projectType + "' ";
            }
            if (query.projectID != null)
            {
                sql += " and projectID='" + query.projectID + "' ";
            }
            if (query.startDate != null && query.startDate != DateTime.MinValue)
            {
                sql += " and startDate>='" + query.startDate + "' ";
            }
            if (query.endDate != null && query.endDate != DateTime.MinValue)
            {
                sql += " and startDate<='" + query.endDate + "' ";
            }
            if (!string.IsNullOrEmpty(roles))
            {
                sql += " and roleID in(select roleID from PMS_RoleList where isDelete='0' and(roleIDs='" + roles + "' or roleIDs like '" + roles + "|%')) ";
            }
            sql += " order by createDate desc";
            return SqlHelper.GetDataByColumnsLst<PMS_Project>(Comm.Config.PMS, sql);
        }



        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="whereStr"></param>
        /// <returns></returns>
        public DataTable GetListTable(PMS_ProjectQuery query, string roles)
        {
            string sql = "select * from dbo.PMS_Project where isDelete='0'";
            if (!string.IsNullOrEmpty(query.projectType))
            {
                sql += " and projectType=N'" + query.projectType + "' ";
            }
            if (query.projectID != null)
            {
                sql += " and projectID='" + query.projectID + "' ";
            }
            if (query.startDate != null && query.startDate != DateTime.MinValue)
            {
                sql += " and startDate>='" + query.startDate + "' ";
            }
            if (query.endDate != null && query.endDate != DateTime.MinValue)
            {
                sql += " and startDate<='" + query.endDate + "' ";
            }
            if (!string.IsNullOrEmpty(roles))
            {
                sql += " and roleID in(select roleID from PMS_RoleList where isDelete='0' and(roleIDs='" + roles + "' or roleIDs like '" + roles + "|%')) ";
            }
            sql += " order by createDate desc";
            return SqlHelper.ExecuteGetTable(Comm.Config.PMS, CommandType.Text, sql);
        }

        #region  Method
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.PMS_Project model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@projectID", SqlDbType.NVarChar,50),
					new SqlParameter("@projectName", SqlDbType.NVarChar,100),
					new SqlParameter("@JDEID", SqlDbType.NVarChar,50),
					new SqlParameter("@sale", SqlDbType.NVarChar,50),
					new SqlParameter("@design", SqlDbType.NVarChar,50),
					new SqlParameter("@systemPath", SqlDbType.NVarChar,50),
					new SqlParameter("@customer", SqlDbType.NVarChar,50),
					new SqlParameter("@customerTel", SqlDbType.NVarChar,50),
					new SqlParameter("@engineer", SqlDbType.NVarChar,50),
					new SqlParameter("@engineerTel", SqlDbType.NVarChar,50),
					new SqlParameter("@construction", SqlDbType.NVarChar,50),
					new SqlParameter("@principal", SqlDbType.NVarChar,50),
					new SqlParameter("@systemProperty", SqlDbType.NVarChar,50),
					new SqlParameter("@startDate", SqlDbType.DateTime),
					new SqlParameter("@endDate", SqlDbType.DateTime),
					new SqlParameter("@provider", SqlDbType.NVarChar,50),
					new SqlParameter("@FMASurveyPoints", SqlDbType.NVarChar,50),
					new SqlParameter("@roleID", SqlDbType.Int,4),
					new SqlParameter("@city", SqlDbType.NVarChar,50),
					new SqlParameter("@projectType", SqlDbType.NVarChar,50)};
            if (string.IsNullOrEmpty(model.projectID))
                parameters[0].Value = DBNull.Value;
            else
                parameters[0].Value = model.projectID;
            if (string.IsNullOrEmpty(model.projectName))
                parameters[1].Value = DBNull.Value;
            else
                parameters[1].Value = model.projectName;
            if (string.IsNullOrEmpty(model.JDEID))
                parameters[2].Value = DBNull.Value;
            else
                parameters[2].Value = model.JDEID;
            if (string.IsNullOrEmpty(model.sale))
                parameters[3].Value = DBNull.Value;
            else
                parameters[3].Value = model.sale;
            if (string.IsNullOrEmpty(model.design))
                parameters[4].Value = DBNull.Value;
            else
                parameters[4].Value = model.design;
            if (string.IsNullOrEmpty(model.systemPath))
                parameters[5].Value = DBNull.Value;
            else
                parameters[5].Value = model.systemPath;
            if (string.IsNullOrEmpty(model.customer))
                parameters[6].Value = DBNull.Value;
            else
                parameters[6].Value = model.customer;
            if (string.IsNullOrEmpty(model.customerTel))
                parameters[7].Value = DBNull.Value;
            else
                parameters[7].Value = model.customerTel;
            if (string.IsNullOrEmpty(model.engineer))
                parameters[8].Value = DBNull.Value;
            else
                parameters[8].Value = model.engineer;
            if (string.IsNullOrEmpty(model.engineerTel))
                parameters[9].Value = DBNull.Value;
            else
                parameters[9].Value = model.engineerTel;
            if (string.IsNullOrEmpty(model.construction))
                parameters[10].Value = DBNull.Value;
            else
                parameters[10].Value = model.construction;
            if (string.IsNullOrEmpty(model.principal))
                parameters[11].Value = DBNull.Value;
            else
                parameters[11].Value = model.principal;
            if (string.IsNullOrEmpty(model.systemProperty))
                parameters[12].Value = DBNull.Value;
            else
                parameters[12].Value = model.systemProperty;
            if (model.startDate == null || model.startDate == DateTime.MinValue)
                parameters[13].Value = DBNull.Value;
            else
                parameters[13].Value = model.startDate;
            if (model.endDate == null || model.endDate == DateTime.MinValue)
                parameters[1].Value = DBNull.Value;
            else
                parameters[14].Value = model.endDate;
            if (string.IsNullOrEmpty(model.provider))
                parameters[15].Value = DBNull.Value;
            else
                parameters[15].Value = model.provider;
            if (string.IsNullOrEmpty(model.FMASurveyPoints))
                parameters[16].Value = DBNull.Value;
            else
                parameters[16].Value = model.FMASurveyPoints;
            if (model.roleID < 0)
                parameters[17].Value = DBNull.Value;
            else
                parameters[17].Value = model.roleID;
            if (string.IsNullOrEmpty(model.city))
                parameters[18].Value = DBNull.Value;
            else
                parameters[18].Value = model.city;
            if (string.IsNullOrEmpty(model.projectType))
                parameters[19].Value = DBNull.Value;
            else
                parameters[19].Value = model.projectType;

            int rows = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.StoredProcedure, "sp_AddProject", parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.PMS_Project model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PMS_Project set ");
            strSql.Append("projectName=@projectName,");
            strSql.Append("JDEID=@JDEID,");
            strSql.Append("sale=@sale,");
            strSql.Append("design=@design,");
            strSql.Append("systemPath=@systemPath,");
            strSql.Append("customer=@customer,");
            strSql.Append("customerTel=@customerTel,");
            strSql.Append("engineer=@engineer,");
            strSql.Append("engineerTel=@engineerTel,");
            strSql.Append("construction=@construction,");
            strSql.Append("principal=@principal,");
            strSql.Append("systemProperty=@systemProperty,");
            strSql.Append("startDate=@startDate,");
            strSql.Append("endDate=@endDate,");
            strSql.Append("provider=@provider,");
            strSql.Append("FMASurveyPoints=@FMASurveyPoints,");
            strSql.Append("roleID=@roleID,");
            strSql.Append("projectType=@projectType,");
            strSql.Append("city=@city");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int),
					new SqlParameter("@projectName", SqlDbType.NVarChar,100),
					new SqlParameter("@JDEID", SqlDbType.NVarChar,50),
					new SqlParameter("@sale", SqlDbType.NVarChar,50),
					new SqlParameter("@design", SqlDbType.NVarChar,50),
					new SqlParameter("@systemPath", SqlDbType.NVarChar,50),
					new SqlParameter("@customer", SqlDbType.NVarChar,50),
					new SqlParameter("@customerTel", SqlDbType.NVarChar,50),
					new SqlParameter("@engineer", SqlDbType.NVarChar,50),
					new SqlParameter("@engineerTel", SqlDbType.NVarChar,50),
					new SqlParameter("@construction", SqlDbType.NVarChar,50),
					new SqlParameter("@principal", SqlDbType.NVarChar,50),
					new SqlParameter("@systemProperty", SqlDbType.NVarChar,50),
					new SqlParameter("@startDate", SqlDbType.DateTime),
					new SqlParameter("@endDate", SqlDbType.DateTime),
					new SqlParameter("@provider", SqlDbType.NVarChar,50),
					new SqlParameter("@FMASurveyPoints", SqlDbType.NVarChar,50),
					new SqlParameter("@roleID", SqlDbType.Int,4),
					new SqlParameter("@city", SqlDbType.NVarChar,50),
					new SqlParameter("@projectType", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.id;
            if (string.IsNullOrEmpty(model.projectName))
                parameters[1].Value = DBNull.Value;
            else
                parameters[1].Value = model.projectName;
            if (string.IsNullOrEmpty(model.JDEID))
                parameters[2].Value = DBNull.Value;
            else
                parameters[2].Value = model.JDEID;
            if (string.IsNullOrEmpty(model.sale))
                parameters[3].Value = DBNull.Value;
            else
                parameters[3].Value = model.sale;
            if (string.IsNullOrEmpty(model.design))
                parameters[4].Value = DBNull.Value;
            else
                parameters[4].Value = model.design;
            if (string.IsNullOrEmpty(model.systemPath))
                parameters[5].Value = DBNull.Value;
            else
                parameters[5].Value = model.systemPath;
            if (string.IsNullOrEmpty(model.customer))
                parameters[6].Value = DBNull.Value;
            else
                parameters[6].Value = model.customer;
            if (string.IsNullOrEmpty(model.customerTel))
                parameters[7].Value = DBNull.Value;
            else
                parameters[7].Value = model.customerTel;
            if (string.IsNullOrEmpty(model.engineer))
                parameters[8].Value = DBNull.Value;
            else
                parameters[8].Value = model.engineer;
            if (string.IsNullOrEmpty(model.engineerTel))
                parameters[9].Value = DBNull.Value;
            else
                parameters[9].Value = model.engineerTel;
            if (string.IsNullOrEmpty(model.construction))
                parameters[10].Value = DBNull.Value;
            else
                parameters[10].Value = model.construction;
            if (string.IsNullOrEmpty(model.principal))
                parameters[11].Value = DBNull.Value;
            else
                parameters[11].Value = model.principal;
            if (string.IsNullOrEmpty(model.systemProperty))
                parameters[12].Value = DBNull.Value;
            else
                parameters[12].Value = model.systemProperty;
            if (model.startDate == null || model.startDate == DateTime.MinValue)
                parameters[13].Value = DBNull.Value;
            else
                parameters[13].Value = model.startDate;
            if (model.endDate == null || model.endDate == DateTime.MinValue)
                parameters[1].Value = DBNull.Value;
            else
                parameters[14].Value = model.endDate;
            if (string.IsNullOrEmpty(model.provider))
                parameters[15].Value = DBNull.Value;
            else
                parameters[15].Value = model.provider;
            if (string.IsNullOrEmpty(model.FMASurveyPoints))
                parameters[16].Value = DBNull.Value;
            else
                parameters[16].Value = model.FMASurveyPoints;
            if (model.roleID < 0)
                parameters[17].Value = DBNull.Value;
            else
                parameters[17].Value = model.roleID;
            if (string.IsNullOrEmpty(model.city))
                parameters[18].Value = DBNull.Value;
            else
                parameters[18].Value = model.city;
            if (string.IsNullOrEmpty(model.projectType))
                parameters[19].Value = DBNull.Value;
            else
                parameters[19].Value = model.projectType;

            int rows = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PMS_Project set isDelete='1' ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = { new SqlParameter("@id", id) };

            int rows = SqlHelper.ExecuteNonQuery(Comm.Config.PMS, CommandType.Text, strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion  Method
    }
}

